#include "dbmanager.h"
#include "../utils/logger.h"
#include <QSqlQuery>
#include <QSqlError>
#include <QVariant>
#include <QFileInfo>
#include <QDir>
#include <QFile>
#include <QTextStream>
#include <QDebug>

DBManager *DBManager::m_instance = nullptr;
QMutex DBManager::m_mutex;

DBManager::DBManager(QObject *parent)
    : QObject(parent), m_connected(false)
{
}

DBManager::~DBManager()
{
    close();
}

DBManager *DBManager::instance()
{
    if (m_instance == nullptr)
    {
        QMutexLocker locker(&m_mutex);
        if (m_instance == nullptr)
        {
            m_instance = new DBManager();
        }
    }
    return m_instance;
}

bool DBManager::initialize(const QString &dbPath)
{
    QMutexLocker locker(&m_mutex);

    if (m_connected)
    {
        LOG_WARNING("数据库已经初始化");
        return true;
    }

    // 如果未指定路径，使用默认路径
    if (dbPath.isEmpty())
    {
        m_dbPath = "C:/Users/86188/Desktop/ModbusPro/QModbusPowerCloud/data/powercloud.db";
        LOG_INFO(QString("使用默认数据库路径: %1").arg(m_dbPath));
    }
    else
    {
        m_dbPath = dbPath;
    }

    // 确保数据库目录存在
    QFileInfo fileInfo(m_dbPath);
    QDir dir = fileInfo.dir();
    if (!dir.exists())
    {
        if (!dir.mkpath("."))
        {
            LOG_ERROR(QString("无法创建数据库目录: %1").arg(dir.path()));
            return false;
        }
    }

    // 创建数据库连接
    m_database = QSqlDatabase::addDatabase("QSQLITE", "PowerCloudConnection");
    m_database.setDatabaseName(m_dbPath);

    if (!m_database.open())
    {
        logDatabaseError("打开数据库失败", m_database.lastError());
        return false;
    }

    LOG_INFO(QString("数据库连接成功: %1").arg(dbPath));

    // 启用外键约束
    QSqlQuery query(m_database);
    query.exec("PRAGMA foreign_keys = ON");

    // 设置性能优化参数
    query.exec("PRAGMA synchronous = NORMAL"); // 平衡性能和安全性
    query.exec("PRAGMA journal_mode = WAL");   // Write-Ahead Logging模式
    query.exec("PRAGMA cache_size = -64000");  // 64MB缓存

    // 创建表结构
    if (!createTables())
    {
        LOG_ERROR("创建数据库表结构失败");
        m_database.close();
        return false;
    }

    m_connected = true;
    LOG_INFO("数据库初始化完成");
    return true;
}

void DBManager::close()
{
    QMutexLocker locker(&m_mutex);

    if (m_connected)
    {
        m_database.close();
        m_connected = false;
        LOG_INFO("数据库连接已关闭");
    }
}

bool DBManager::isConnected() const
{
    return m_connected && m_database.isOpen();
}

bool DBManager::createTables()
{
    QStringList sqlStatements;

    // 1. 电表信息表
    sqlStatements << R"(
        CREATE TABLE IF NOT EXISTS meters (
            meter_id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            location TEXT,
            bus_id INTEGER NOT NULL,
            enabled INTEGER DEFAULT 1,
            create_time TEXT NOT NULL,
            update_time TEXT NOT NULL
        )
    )";

    // 2. 历史数据表
    sqlStatements << R"(
        CREATE TABLE IF NOT EXISTS history_data (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            meter_id INTEGER NOT NULL,
            ua REAL NOT NULL,
            ub REAL NOT NULL,
            uc REAL NOT NULL,
            ia REAL NOT NULL,
            ib REAL NOT NULL,
            ic REAL NOT NULL,
            timestamp TEXT NOT NULL,
            FOREIGN KEY (meter_id) REFERENCES meters(meter_id)
        )
    )";

    // 创建历史数据索引（优化查询性能）
    sqlStatements << "CREATE INDEX IF NOT EXISTS idx_history_meter_time ON history_data(meter_id, timestamp)";
    sqlStatements << "CREATE INDEX IF NOT EXISTS idx_history_time ON history_data(timestamp)";

    // 3. 报警记录表
    sqlStatements << R"(
        CREATE TABLE IF NOT EXISTS alarm_records (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            meter_id INTEGER NOT NULL,
            alarm_type TEXT NOT NULL,
            alarm_value REAL NOT NULL,
            threshold REAL NOT NULL,
            timestamp TEXT NOT NULL,
            acknowledged INTEGER DEFAULT 0,
            ack_time TEXT,
            FOREIGN KEY (meter_id) REFERENCES meters(meter_id)
        )
    )";

    // 创建报警记录索引
    sqlStatements << "CREATE INDEX IF NOT EXISTS idx_alarm_meter_time ON alarm_records(meter_id, timestamp)";
    sqlStatements << "CREATE INDEX IF NOT EXISTS idx_alarm_ack ON alarm_records(acknowledged)";

    // 执行所有SQL语句
    QSqlQuery query(m_database);
    for (const QString &sql : sqlStatements)
    {
        if (!query.exec(sql))
        {
            logDatabaseError("创建表失败", query.lastError());
            return false;
        }
    }

    LOG_INFO("数据库表结构创建成功");
    return true;
}

// ============ 电表管理 ============

bool DBManager::saveMeterInfo(const MeterInfo &info)
{
    QMutexLocker locker(&m_mutex);

    QSqlQuery query(m_database);
    query.prepare(R"(
        INSERT OR REPLACE INTO meters 
        (meter_id, name, location, bus_id, enabled, create_time, update_time)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    )");

    query.addBindValue(info.meterId);
    query.addBindValue(info.name);
    query.addBindValue(info.location);
    query.addBindValue(info.busId);
    query.addBindValue(info.enabled ? 1 : 0);
    query.addBindValue(info.createTime.toString("yyyy-MM-dd HH:mm:ss"));
    query.addBindValue(info.updateTime.toString("yyyy-MM-dd HH:mm:ss"));

    if (!query.exec())
    {
        logDatabaseError("保存电表信息失败", query.lastError());
        return false;
    }

    return true;
}

QVector<MeterInfo> DBManager::getAllMeterInfo()
{
    QMutexLocker locker(&m_mutex);
    QVector<MeterInfo> result;

    QSqlQuery query(m_database);
    if (!query.exec("SELECT meter_id, name, location, bus_id, enabled, create_time, update_time FROM meters"))
    {
        logDatabaseError("查询电表信息失败", query.lastError());
        return result;
    }

    while (query.next())
    {
        MeterInfo info;
        info.meterId = query.value(0).toInt();
        info.name = query.value(1).toString();
        info.location = query.value(2).toString();
        info.busId = query.value(3).toInt();
        info.enabled = query.value(4).toBool();
        info.createTime = QDateTime::fromString(query.value(5).toString(), "yyyy-MM-dd HH:mm:ss");
        info.updateTime = QDateTime::fromString(query.value(6).toString(), "yyyy-MM-dd HH:mm:ss");
        result.append(info);
    }

    return result;
}

// ============ 历史数据管理 ============

bool DBManager::insertHistoryData(const HistoryData &data)
{
    QMutexLocker locker(&m_mutex);

    QSqlQuery query(m_database);
    query.prepare(R"(
        INSERT INTO history_data 
        (meter_id, ua, ub, uc, ia, ib, ic, timestamp)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    )");

    query.addBindValue(data.meterId);
    query.addBindValue(data.ua);
    query.addBindValue(data.ub);
    query.addBindValue(data.uc);
    query.addBindValue(data.ia);
    query.addBindValue(data.ib);
    query.addBindValue(data.ic);
    query.addBindValue(data.timestamp.toString("yyyy-MM-dd HH:mm:ss"));

    if (!query.exec())
    {
        logDatabaseError("插入历史数据失败", query.lastError());
        return false;
    }

    return true;
}

int DBManager::batchInsertHistoryData(const QVector<HistoryData> &dataList)
{
    if (dataList.isEmpty())
    {
        return 0;
    }

    QMutexLocker locker(&m_mutex);

    // 开启事务提高批量插入性能
    if (!m_database.transaction())
    {
        LOG_ERROR("开启事务失败");
        return 0;
    }

    QSqlQuery query(m_database);
    query.prepare(R"(
        INSERT INTO history_data 
        (meter_id, ua, ub, uc, ia, ib, ic, timestamp)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    )");

    int successCount = 0;
    for (const HistoryData &data : dataList)
    {
        query.addBindValue(data.meterId);
        query.addBindValue(data.ua);
        query.addBindValue(data.ub);
        query.addBindValue(data.uc);
        query.addBindValue(data.ia);
        query.addBindValue(data.ib);
        query.addBindValue(data.ic);
        query.addBindValue(data.timestamp.toString("yyyy-MM-dd HH:mm:ss"));

        if (query.exec())
        {
            successCount++;
        }
        else
        {
            logDatabaseError("批量插入历史数据失败", query.lastError());
        }
    }

    // 提交事务
    if (!m_database.commit())
    {
        LOG_ERROR("提交事务失败，回滚");
        m_database.rollback();
        return 0;
    }

    LOG_INFO(QString("批量插入历史数据成功: %1/%2").arg(successCount).arg(dataList.size()));
    emit historyDataInserted(successCount);

    return successCount;
}

// 查询历史数据
QVector<HistoryData> DBManager::queryHistoryData(int meterId,
                                                 const QDateTime &startTime,
                                                 const QDateTime &endTime)
{
    QMutexLocker locker(&m_mutex);
    QVector<HistoryData> result;

    QSqlQuery query(m_database);
    QString sql;

    if (meterId == -1)
    {
        sql = "SELECT meter_id, ua, ub, uc, ia, ib, ic, timestamp FROM history_data WHERE timestamp >= ? AND timestamp <= ? ORDER BY timestamp";
        query.prepare(sql);
        query.addBindValue(startTime.toString("yyyy-MM-dd HH:mm:ss"));
        query.addBindValue(endTime.toString("yyyy-MM-dd HH:mm:ss"));
    }
    else
    {
        sql = "SELECT meter_id, ua, ub, uc, ia, ib, ic, timestamp FROM history_data WHERE meter_id = ? AND timestamp >= ? AND timestamp <= ? ORDER BY timestamp";
        query.prepare(sql);
        query.addBindValue(meterId);
        query.addBindValue(startTime.toString("yyyy-MM-dd HH:mm:ss"));
        query.addBindValue(endTime.toString("yyyy-MM-dd HH:mm:ss"));
    }

    if (!query.exec())
    {
        logDatabaseError("查询历史数据失败", query.lastError());
        return result;
    }

    while (query.next())
    {
        HistoryData data;
        data.meterId = query.value(0).toInt();
        data.ua = query.value(1).toDouble();
        data.ub = query.value(2).toDouble();
        data.uc = query.value(3).toDouble();
        data.ia = query.value(4).toDouble();
        data.ib = query.value(5).toDouble();
        data.ic = query.value(6).toDouble();
        data.timestamp = QDateTime::fromString(query.value(7).toString(), "yyyy-MM-dd HH:mm:ss");
        result.append(data);
    }

    return result;
}

QVector<HistoryData> DBManager::queryHistoryDataPaged(int meterId,
                                                      const QDateTime &startTime,
                                                      const QDateTime &endTime,
                                                      int offset,
                                                      int limit)
{
    QMutexLocker locker(&m_mutex);
    QVector<HistoryData> result;

    QSqlQuery query(m_database);
    query.prepare(R"(
        SELECT meter_id, ua, ub, uc, ia, ib, ic, timestamp 
        FROM history_data 
        WHERE meter_id = ? AND timestamp >= ? AND timestamp <= ?
        ORDER BY timestamp
        LIMIT ? OFFSET ?
    )");

    query.addBindValue(meterId);
    query.addBindValue(startTime.toString("yyyy-MM-dd HH:mm:ss"));
    query.addBindValue(endTime.toString("yyyy-MM-dd HH:mm:ss"));
    query.addBindValue(limit);
    query.addBindValue(offset);

    if (!query.exec())
    {
        logDatabaseError("分页查询历史数据失败", query.lastError());
        return result;
    }

    while (query.next())
    {
        HistoryData data;
        data.meterId = query.value(0).toInt();
        data.ua = query.value(1).toDouble();
        data.ub = query.value(2).toDouble();
        data.uc = query.value(3).toDouble();
        data.ia = query.value(4).toDouble();
        data.ib = query.value(5).toDouble();
        data.ic = query.value(6).toDouble();
        data.timestamp = QDateTime::fromString(query.value(7).toString(), "yyyy-MM-dd HH:mm:ss");
        result.append(data);
    }

    return result;
}

int DBManager::getHistoryDataCount(int meterId,
                                   const QDateTime &startTime,
                                   const QDateTime &endTime)
{
    QMutexLocker locker(&m_mutex);

    QSqlQuery query(m_database);
    QString sql;

    if (meterId == -1)
    {
        sql = "SELECT COUNT(*) FROM history_data WHERE timestamp >= ? AND timestamp <= ?";
        query.prepare(sql);
        query.addBindValue(startTime.toString("yyyy-MM-dd HH:mm:ss"));
        query.addBindValue(endTime.toString("yyyy-MM-dd HH:mm:ss"));
    }
    else
    {
        sql = "SELECT COUNT(*) FROM history_data WHERE meter_id = ? AND timestamp >= ? AND timestamp <= ?";
        query.prepare(sql);
        query.addBindValue(meterId);
        query.addBindValue(startTime.toString("yyyy-MM-dd HH:mm:ss"));
        query.addBindValue(endTime.toString("yyyy-MM-dd HH:mm:ss"));
    }

    if (!query.exec() || !query.next())
    {
        logDatabaseError("查询历史数据数量失败", query.lastError());
        return 0;
    }

    return query.value(0).toInt();
}

int DBManager::deleteHistoryData(const QDateTime &startTime, const QDateTime &endTime)
{
    QMutexLocker locker(&m_mutex);

    QSqlQuery query(m_database);
    query.prepare("DELETE FROM history_data WHERE timestamp >= ? AND timestamp <= ?");
    query.addBindValue(startTime.toString("yyyy-MM-dd HH:mm:ss"));
    query.addBindValue(endTime.toString("yyyy-MM-dd HH:mm:ss"));

    if (!query.exec())
    {
        logDatabaseError("删除历史数据失败", query.lastError());
        return 0;
    }

    return query.numRowsAffected();
}

// ============ 报警记录管理 ============

int DBManager::insertAlarmRecord(const AlarmRecord &record)
{
    QMutexLocker locker(&m_mutex);

    QSqlQuery query(m_database);
    query.prepare(R"(
        INSERT INTO alarm_records 
        (meter_id, alarm_type, alarm_value, threshold, timestamp, acknowledged, ack_time)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    )");

    query.addBindValue(record.meterId);
    query.addBindValue(record.alarmType);
    query.addBindValue(record.alarmValue);
    query.addBindValue(record.threshold);
    query.addBindValue(record.timestamp.toString("yyyy-MM-dd HH:mm:ss"));
    query.addBindValue(record.acknowledged ? 1 : 0);
    query.addBindValue(record.ackTime.isValid() ? record.ackTime.toString("yyyy-MM-dd HH:mm:ss") : QVariant());

    if (!query.exec())
    {
        logDatabaseError("插入报警记录失败", query.lastError());
        return -1;
    }

    int alarmId = query.lastInsertId().toInt();
    emit alarmRecordInserted(alarmId);
    return alarmId;
}

QVector<AlarmRecord> DBManager::queryAlarmRecords(int meterId,
                                                  const QDateTime &startTime,
                                                  const QDateTime &endTime,
                                                  int acknowledged)
{
    QMutexLocker locker(&m_mutex);
    QVector<AlarmRecord> result;

    QString sql = "SELECT id, meter_id, alarm_type, alarm_value, threshold, timestamp, acknowledged, ack_time FROM alarm_records WHERE timestamp >= ? AND timestamp <= ?";

    QSqlQuery query(m_database);

    if (meterId != -1)
    {
        sql += " AND meter_id = ?";
    }

    if (acknowledged != -1)
    {
        sql += " AND acknowledged = ?";
    }

    sql += " ORDER BY timestamp DESC";

    query.prepare(sql);
    query.addBindValue(startTime.toString("yyyy-MM-dd HH:mm:ss"));
    query.addBindValue(endTime.toString("yyyy-MM-dd HH:mm:ss"));

    if (meterId != -1)
    {
        query.addBindValue(meterId);
    }

    if (acknowledged != -1)
    {
        query.addBindValue(acknowledged);
    }

    if (!query.exec())
    {
        logDatabaseError("查询报警记录失败", query.lastError());
        return result;
    }

    while (query.next())
    {
        AlarmRecord record;
        record.id = query.value(0).toInt();
        record.meterId = query.value(1).toInt();
        record.alarmType = query.value(2).toString();
        record.alarmValue = query.value(3).toDouble();
        record.threshold = query.value(4).toDouble();
        record.timestamp = QDateTime::fromString(query.value(5).toString(), "yyyy-MM-dd HH:mm:ss");
        record.acknowledged = query.value(6).toBool();
        if (!query.value(7).isNull())
        {
            record.ackTime = QDateTime::fromString(query.value(7).toString(), "yyyy-MM-dd HH:mm:ss");
        }
        result.append(record);
    }

    return result;
}

bool DBManager::acknowledgeAlarm(int alarmId)
{
    QMutexLocker locker(&m_mutex);

    QSqlQuery query(m_database);
    query.prepare("UPDATE alarm_records SET acknowledged = 1, ack_time = ? WHERE id = ?");
    query.addBindValue(QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss"));
    query.addBindValue(alarmId);

    if (!query.exec())
    {
        logDatabaseError("确认报警失败", query.lastError());
        return false;
    }

    return query.numRowsAffected() > 0;
}

int DBManager::batchAcknowledgeAlarms(const QVector<int> &alarmIds)
{
    if (alarmIds.isEmpty())
    {
        return 0;
    }

    QMutexLocker locker(&m_mutex);

    if (!m_database.transaction())
    {
        LOG_ERROR("开启事务失败");
        return 0;
    }

    QSqlQuery query(m_database);
    query.prepare("UPDATE alarm_records SET acknowledged = 1, ack_time = ? WHERE id = ?");

    int successCount = 0;
    QString ackTime = QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss");

    for (int alarmId : alarmIds)
    {
        query.addBindValue(ackTime);
        query.addBindValue(alarmId);

        if (query.exec() && query.numRowsAffected() > 0)
        {
            successCount++;
        }
    }

    if (!m_database.commit())
    {
        LOG_ERROR("提交事务失败，回滚");
        m_database.rollback();
        return 0;
    }

    return successCount;
}

bool DBManager::deleteAlarmRecord(int alarmId)
{
    QMutexLocker locker(&m_mutex);

    QSqlQuery query(m_database);
    query.prepare("DELETE FROM alarm_records WHERE id = ?");
    query.addBindValue(alarmId);

    if (!query.exec())
    {
        logDatabaseError("删除报警记录失败", query.lastError());
        return false;
    }

    return query.numRowsAffected() > 0;
}

int DBManager::getUnacknowledgedAlarmCount()
{
    QMutexLocker locker(&m_mutex);

    QSqlQuery query(m_database);
    if (!query.exec("SELECT COUNT(*) FROM alarm_records WHERE acknowledged = 0") || !query.next())
    {
        logDatabaseError("查询未确认报警数量失败", query.lastError());
        return 0;
    }

    return query.value(0).toInt();
}

// ============ 数据库维护 ============

int DBManager::cleanOldData(int days)
{
    QMutexLocker locker(&m_mutex);

    QDateTime cutoffTime = QDateTime::currentDateTime().addDays(-days);

    QSqlQuery query(m_database);
    query.prepare("DELETE FROM history_data WHERE timestamp < ?");
    query.addBindValue(cutoffTime.toString("yyyy-MM-dd HH:mm:ss"));

    if (!query.exec())
    {
        logDatabaseError("清理旧数据失败", query.lastError());
        return 0;
    }

    int deletedCount = query.numRowsAffected();
    LOG_INFO(QString("清理 %1 天前的数据，删除 %2 条记录").arg(days).arg(deletedCount));

    return deletedCount;
}

bool DBManager::optimizeDatabase()
{
    QMutexLocker locker(&m_mutex);

    QSqlQuery query(m_database);
    if (!query.exec("VACUUM"))
    {
        logDatabaseError("优化数据库失败", query.lastError());
        return false;
    }

    LOG_INFO("数据库优化完成");
    return true;
}

qint64 DBManager::getDatabaseSize() const
{
    QFileInfo fileInfo(m_dbPath);
    return fileInfo.size();
}

int DBManager::getTableRowCount(const QString &tableName)
{
    QMutexLocker locker(&m_mutex);

    QSqlQuery query(m_database);
    query.prepare(QString("SELECT COUNT(*) FROM %1").arg(tableName));

    if (!query.exec() || !query.next())
    {
        logDatabaseError("查询表记录数失败", query.lastError());
        return 0;
    }

    return query.value(0).toInt();
}

bool DBManager::executeSQL(const QString &sql, const QVariantList &bindValues)
{
    QSqlQuery query(m_database);
    query.prepare(sql);

    for (const QVariant &value : bindValues)
    {
        query.addBindValue(value);
    }

    if (!query.exec())
    {
        logDatabaseError("执行SQL失败", query.lastError());
        return false;
    }

    return true;
}

void DBManager::logDatabaseError(const QString &context, const QSqlError &error)
{
    QString errorMsg = QString("%1: %2").arg(context).arg(error.text());
    LOG_ERROR(errorMsg);
    emit databaseError(errorMsg);
}
